﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace CNative.Dapper.Utils
{
    public static class WhereExpr//: ISqlExpr
    {
        #region 扩展方法
        /// <summary>
        /// LambdaToSql 中扩展
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="array"></param>
        /// <returns></returns>
        public static bool In<T>(this T obj, List<T> array)
        {
            return obj.In_(array);
        }
        /// <summary>
        /// LambdaToSql 中扩展
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="array"></param>
        /// <returns></returns>
        public static bool In<T>(this T obj, params T[] array)
        {
            return obj.In_(array);
        }
        /// <summary>
        /// LambdaToSql 中扩展
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="array"></param>
        /// <returns></returns>
        public static bool NotIn<T>(this T obj, List<T> array)
        {
            return !obj.In_(array);
        }
        /// <summary>
        /// LambdaToSql 中扩展
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="array"></param>
        /// <returns></returns>
        public static bool NotIn<T>(this T obj, params T[] array)
        {
            return !obj.In_(array);
        }
        /// <summary>
        /// LambdaToSql 中扩展
        /// </summary>
        /// <param name="str"></param>
        /// <param name="likeStr"></param>
        /// <returns></returns>
        public static bool StartLike(this string str, string likeStr)
        {
            return likeStr?.Contains(str) == true;
        }
        /// <summary>
        /// LambdaToSql 中扩展
        /// </summary>
        /// <param name="str"></param>
        /// <param name="likeStr"></param>
        /// <returns></returns>
        public static bool EndLike(this string str, string likeStr)
        {
            return likeStr?.Contains(str) == true;
        }
        /// <summary>
        /// LambdaToSql 中扩展
        /// </summary>
        /// <param name="str"></param>
        /// <param name="likeStr"></param>
        /// <returns></returns>
        public static bool Like(this string str, string likeStr)
        {
            return likeStr?.Contains(str) == true;
        }
        /// <summary>
        /// LambdaToSql 中扩展
        /// </summary>
        /// <param name="str"></param>
        /// <param name="likeStr"></param>
        /// <returns></returns>
        public static bool NotLike(this string str, string likeStr)
        {
            return likeStr != null && likeStr.Contains(str) == false;
        }
        /// <summary>
        /// LambdaToSql 中扩展
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="i"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public static bool Between<T>(this T i, T start, T end) where T : IComparable<T>
        {
            return i.CompareTo(start) >= 0 && i.CompareTo(end) <= 0;
        }
        #endregion

        #region BuildSql
        internal static string BuildSql(string methodName, ReadOnlyCollection<Expression> arguments, SqlEntity sqlent, DbColumnInfo column, DbTableInfo tableInfo,string tableAlias = "",string paramSuffix = "wp_")
        {
            var format = "";
            object value = null;

            #region switch (methodName)
            switch (methodName)
            {
                case "StartLike":
                    if (sqlent.DbHelper.DBType == DatabaseType.MsAccess)
                    {
                        format += " LIKE \"*{0}\" ";

                        value = ExpressionHelper.EvaluateValue(arguments[1]);
                        format = Funs.FormatFieldName(tableAlias, column.Name, sqlent) + format;
                        return string.Format(format, value);
                    }
                    else
                    {
                        format += " LIKE %{0} ";
                        value = ExpressionHelper.EvaluateValue(arguments[1]);
                    }
                    break;
                case "EndLike":
                    if (sqlent.DbHelper.DBType == DatabaseType.MsAccess)
                    {
                        format += " LIKE \"{0}*\" ";

                        value = ExpressionHelper.EvaluateValue(arguments[1]);
                        format = Funs.FormatFieldName(tableAlias, column.Name, sqlent) + format;
                        return string.Format(format, value);
                    }
                    else
                    {
                        format += " LIKE {0}% ";
                        value = ExpressionHelper.EvaluateValue(arguments[1]);
                    }
                    break;
                case "Like":
                    if (sqlent.DbHelper.DBType == DatabaseType.MsAccess)
                    {
                        format += " LIKE \"*{0}*\" ";

                        value = ExpressionHelper.EvaluateValue(arguments[1]);
                        format = Funs.FormatFieldName(tableAlias, column.Name, sqlent) + format;
                        return string.Format(format, value);
                    }
                    else
                    {
                        format += " LIKE %{0}% ";
                        value = ExpressionHelper.EvaluateValue(arguments[1]);
                    }
                    break;
                case "NotLike":
                    if (sqlent.DbHelper.DBType == DatabaseType.MsAccess)
                    {
                        format += " NOT LIKE \"*{0}*\" ";

                        value = ExpressionHelper.EvaluateValue(arguments[1]);
                        format = Funs.FormatFieldName(tableAlias, column.Name, sqlent) + format;
                        return string.Format(format, value);
                    }
                    else
                    {
                        format += " NOT LIKE %{0}% ";
                        value = ExpressionHelper.EvaluateValue(arguments[1]);
                    }
                    break;
                case "Equals":
                    format += " ={0} ";
                    value = ExpressionHelper.EvaluateValue(arguments[0]);
                    break;
                case "Between":
                    format += " BETWEEN {0} AND {1}";
                    value = ExpressionHelper.EvaluateValue(arguments[1]);
                    var value2 = ExpressionHelper.EvaluateValue(arguments[2]);
                    if (value.IsNotNullOrEmpty() && value2.IsNotNullOrEmpty())
                    {
                        if (tableInfo == null) tableInfo = Funs.GetDbTableInfo(sqlent.DbHelper, column.PropertyInfo?.DeclaringType);
                        format = Funs.FormatFieldName(tableAlias, column.Name, sqlent) + format;
                        if (ExpressionHelper.AddDbParameter(column.Name, value, sqlent, out string parameterName1, paramSuffix, tableInfo))
                        {
                            if (ExpressionHelper.AddDbParameter(column.Name, value2, sqlent, out string parameterName2, paramSuffix, tableInfo))
                                return string.Format(format, parameterName1, parameterName2);
                        }
                    }
                    return " 0=1 ";
                case "In":
                case "NotIn":
                    {
                        if (methodName == "In")
                            format += " IN ({0}) ";//SELECT * FROM 
                        else
                            format += " NOT IN ({0}) ";//SELECT * FROM 

                        value = ExpressionHelper.EvaluateValue(arguments[1]);
                        if (value is Array || value is System.Collections.IList)
                        {
                            var listobj = (value as System.Collections.IEnumerable).Cast<object>().Select(x => x).ToList();
                            if (listobj.IsNullOrEmpty() || listobj.Count == 0)
                            {
                                return " 0=1 ";//In 中为空时，直接返回false
                            }
                            else
                            {
                                format = Funs.FormatFieldName(tableAlias, column.Name, sqlent) + format;
                                return string.Format(format, Funs.ListToSqlStr(listobj));
                            }
                        }
                        else return " 0=1 ";
                    }
                    //break;
                    //format += " NOT IN ({0}) ";//SELECT * FROM 
                    //value = ExpressionHelper.EvaluateValue(arguments[1]);
                    //if (value is Array || value is System.Collections.IList)
                    //{
                    //    var listobj = (value as System.Collections.IEnumerable).Cast<object>().Select(x => x).ToList();
                    //    if (listobj.IsNullOrEmpty() || listobj.Count == 0)
                    //    {
                    //        return " 0=1 ";//NotIn 中为空时，直接返回false
                    //    }
                    //}
                    //else return " 0=1 ";
                    //break;
                case "SQL_AVG":
                case "SQL_SUM":
                case "SQL_MAX":
                case "SQL_MIN":
                case "SQL_COUNT":

                case "SQL_UCASE":
                case "SQL_LCASE":
                case "SQL_NVL":
                    object[] paras = null;
                    if (arguments.Count > 0)
                    {
                        paras = new object[arguments.Count];
                        for (var i = 0; i < arguments.Count; i++)
                        {
                            paras[i] = ExpressionHelper.EvaluateValue(arguments[i]);
                            if (paras[i] is System.Reflection.MemberInfo mb)
                                paras[i] = Funs.FormatFieldName(tableAlias, mb.GetColumnName(), sqlent); //string.Format(format, mb.Name);
                            else
                                paras[i] = paras[i].ToSqlValue();
                        }
                    }
                    return sqlent.DbHelper.SqlDbProvider.InvokeMethod(methodName, paras)?.ToString();
                default:
                    return null;
            }
            #endregion

            if (tableInfo == null) tableInfo = Funs.GetDbTableInfo(sqlent.DbHelper, column.PropertyInfo?.DeclaringType);
            format = Funs.FormatFieldName(tableAlias, column.Name,sqlent) + format;
            if (value.IsNotNullOrEmpty() && ExpressionHelper.AddDbParameter(column.Name, value, sqlent, out string parameterName, paramSuffix, tableInfo))
            {
                return string.Format(format, parameterName);
            }
            return null;
        }
        #endregion
    }

    public static class FieldExpr//: ISqlExpr
    {
        #region 扩展方法
        /// <summary>
        /// SQL AVG() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的值的平均值</returns>
        public static T SQL_AVG<T>(this T column_name)
        {
            return column_name;
        }
        /// <summary>
        /// SQL COUNT() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列值的数目</returns>
        public static T SQL_COUNT<T>(this T column_name)
        {
            return column_name;
        }
        /// <summary>
        /// SQL MAX() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的最大值</returns>
        public static T SQL_MAX<T>(this T column_name)
        {
            return column_name;
        }
        /// <summary>
        /// SQL MIN() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的最小值</returns>
        public static T SQL_MIN<T>(this T column_name)
        {
            return column_name;
        }
        /// <summary>
        /// SQL SUM() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的总数</returns>
        public static T SQL_SUM<T>(this T column_name)
        {
            return column_name;
        }

        /// <summary>
        /// SQL NVL() 从两个表达式返回一个非 null 值	函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="check_expression">将被检查是否为 NULL的表达式。可以是任何类型的。</param>
        /// <param name="replacement_value">在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。</param>
        /// <returns>如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value</returns>
        public static T SQL_NVL<T>(this T check_expression, T replacement_value)
        {
            return check_expression.IsNullOrEmpty() ? replacement_value : check_expression;
        }
        /// <summary>
        /// SQL NOW() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>NOW() 函数返回当前系统的日期和时间</returns>
        public static DateTime SQL_NOW(this DateTime column_name)
        {
            return DateTime.Now;
        }
        /// <summary>
          /// SQL NOW() 函数
          /// </summary>
          /// <typeparam name="T"></typeparam>
          /// <param name="column_name">列名</param>
          /// <returns>NOW() 函数返回当前系统的日期和时间</returns>
        public static string SQL_NOW(this string column_name)
        {
            return DateTime.Now.ToString();
        }

        /// <summary>
        /// SQL UCASE() 函数
        /// </summary>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为大写</returns>
        public static string SQL_UCASE(this string column_name)
        {
            return column_name?.ToUpper();
        }

        /// <summary>
        /// SQL LCASE() 函数
        /// </summary>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为小写</returns>
        public static string SQL_LCASE(this string column_name)
        {
            return column_name?.ToLower();
        }

        /// <summary>
        /// SQL LEFT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public static string SQL_LEFT(this string str,int length)
        {
            return str?.Substring(0, length);
        }
        /// <summary>
        /// SQL RIGHT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public static string SQL_RIGHT(this string str, int length)
        {
            return str?.Substring(str.Length - length, length);
        }
        /// <summary>
        /// SQL SUBSTR(s, start, length) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>从字符串 str 的 start 位置截取长度为 length 的子字符串</returns>
        public static string SQL_SUBSTR(this string str,int start, int length)
        {
            return str?.Substring(str.Length - length, length);
        }
        /// <summary>
        /// SQL TRIM(s) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>去掉字符串 str 开始和结尾处的空格</returns>
        public static string SQL_TRIM(this string str)
        {
            return str?.Trim();
        }
        /// <summary>
        /// SQL REPLACE(s,s1,s2) 函数
        /// </summary>
        /// <param name="s">列名</param>
        /// <returns>将字符串 s2 替代字符串 s 中的字符串 s1</returns>
        public static string SQL_REPLACE(this string s, string s1, string s2)
        {
            return s?.Replace(s1, s2);
        }

        /// <summary>
        /// SQL CAST() 转换数据类型	函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <param name="type">数据类型</param>
        /// <returns>转换数据类型</returns>
        public static T SQL_CAST<T>(this T column_name,string type)
        {
            return column_name;
        }

        public static int SQL_ToInt32(this string column_name)
        {
            return column_name.ToInt_();
        }

        public static long SQL_ToInt64(this string column_name)
        {
            return column_name.ToLong_();
        }

        public static string SQL_ToVarchar(this object column_name)
        {
            return column_name.NullToStr();
        }

        public static Guid SQL_ToGuid(this string column_name)
        {
            return column_name.ToGuid_();
        }

        public static double SQL_ToDouble(this string column_name)
        {
            return column_name.ToDouble_();
        }
        /// <summary>转换高精度的十进制数（一般用于货币）</summary>
        /// <param name="column_name"></param>
        /// <param name="d">小数位数</param>
        /// <returns></returns>
        public static decimal SQL_ToDecimal(this string column_name,int d)
        {
            return column_name.ToDecimal_();
        }
        public static bool SQL_ToBool(this object column_name)
        {
            return column_name.ToBool_();
        }

        public static DateTime SQL_ToDate(this string column_name)
        {
            return column_name.ToDate_();
        }

        public static DateTime SQL_ToDateShort(this string column_name)
        {
            return column_name.ToDate_();
        }

        public static DateTime SQL_ToTime(this string column_name)
        {
            return column_name.ToDate_();
        }
        #endregion

        #region BuildSql
        internal static string BuildSql(BaseProvider dbProvider, string methodName, ReadOnlyCollection<Expression> arguments, string tableAlias = "", string paramSuffix = "wp_")
        {
            #region switch (methodName)
            switch (methodName)
            {
                case "SQL_AVG":
                case "SQL_SUM":
                case "SQL_MAX":
                case "SQL_MIN":
                case "SQL_COUNT":

                case "SQL_UCASE":
                case "SQL_LCASE":
                case "SQL_NVL":

                case "SQL_LEFT":
                case "SQL_RIGHT":
                case "SQL_SUBSTR":
                case "SQL_TRIM": 
                case "SQL_REPLACE":
                case "SQL_CAST":

                case "SQL_ToInt32":
                case "SQL_ToInt64":
                case "SQL_ToDouble":
                case "SQL_ToDecimal":
                case "SQL_ToBool":
                case "SQL_ToVarchar":
                case "SQL_ToGuid":
                case "SQL_ToDate":
                case "SQL_ToDateShort":
                case "SQL_ToTime":
                    object[] paras = null;
                    if (arguments.Count > 0)
                    {
                        paras = new object[arguments.Count];
                        for (var i = 0; i < arguments.Count; i++)
                        {
                            paras[i] = ExpressionHelper.EvaluateValue(arguments[i]);
                            if (paras[i] is System.Reflection.MemberInfo mb)
                                paras[i] = Funs.FormatFieldName(tableAlias, mb.GetColumnName(), dbProvider);// mb.Name;
                            else
                                paras[i] = paras[i].ToSqlValue();
                        }
                    }
                    return dbProvider.InvokeMethod(methodName, paras)?.ToString();
                case "SQL_NOW":
                    return dbProvider.SQL_NOW();
                default:
                    return null;
            }
            #endregion
        }
        #endregion
    }
}
